#!/usr/bin/env python
# -*- coding: utf-8 -*-
#===============================================================================
# Copyright 2011 zod.yslin
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# 
# Author: zod.yslin
# Email: 
# File Name: demo.py
# Description: 
#   Manual and downloads: http://pypi.python.org/pypi/MySQL-python/#downloads
#   http://www.python.org/dev/peps/pep-0249/
#   http://www.tutorialspoint.com/python/python_database_access.htm
#   drop, create, select, insert, update, delete.
#   
# Edit History: 
#   2011-10-03    File created.
#===============================================================================
#!/usr/bin/python
 
# 引入 MySQL 模組
import MySQLdb
import sys, traceback
 
# 連接到 MySQL
db = MySQLdb.connect(host="localhost", user="phpmyadmin", passwd="123456", db="phpmyadmin")
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)

# Prepare SQL query to INSERT many records into the database.
sql = """INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (%s, %s, %s, %s, %s)"""

row = [
        ('Jecy', 'Carter', 18, 'M', 500),
        ('Zod', 'Lin', 30, 'F', 5000)
    ]
try:
   # Execute the SQL command
   cursor.executemany(sql, row)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
   print "Exception in user code:"
   print '-'*60
   traceback.print_exc(file=sys.stdout)
   print '-'*60

# Prepare SQL query to INSERT a record into the database, dynamically
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# Prepare SQL query to QUERY a record into the database.
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fecth data"

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# Prepare SQL query to QUERY a record into the database.
sql = "SELECT * FROM EMPLOYEE"
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      print row
except:
   print "Error: unable to fecth data"


cursor.close()
# disconnect from server
db.close()
